﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FDSimpleModelGenerator.Common;
using FDSimpleModelGenerator.Entities;
using FDSimpleModelGenerator.Entities.CreateTables;
using FDSimpleModelGenerator.Entities.Enums;
using FDSimpleModelGenerator.IServices;

namespace FDSimpleModelGenerator.Services.SqlServer
{
    /// <summary>
    /// Sql Server生成数据库表服务
    /// </summary>
    public class SqlServerGenerateTableService : IGenerateTableService
    {
        private readonly IDbAccessor _dbAccessor;
        private readonly IDbConfigResolver _dbConfigResolver;

        public DbType DbType => DbType.SqlServer;

        public SqlServerGenerateTableService(IDbAccessor dbAccessor, IEnumerable<IDbConfigResolver> dbConfigResolvers)
        {
            _dbAccessor = dbAccessor;
            _dbConfigResolver = dbConfigResolvers.First(r => r.DbType == DbType);
        }

        public GenResult Generate(DbConfig dbConfig, Table tableInfo)
        {
            _dbAccessor.Init(() => new SqlConnection(_dbConfigResolver.GetConnectionString(dbConfig)));

            var sb = new StringBuilder();
            var sbComment = new StringBuilder();
            sb.Append($"USE {dbConfig.Database};\r\n");
            sb.Append($"--{tableInfo.Description}\r\n");
            if (tableInfo.DeleteExists)
            {
                sb.Append($"IF OBJECT_ID('{tableInfo.Name}', 'U') IS NOT NULL \r\n");
                sb.Append($"\tDROP TABLE [{tableInfo.Name}];\r\n");
            }
            sb.Append($"--{tableInfo.Description}\r\n");
            sb.Append($"CREATE TABLE [{tableInfo.Name}](\r\n");

            sbComment.Append(
                $"EXEC sp_addextendedproperty N'MS_Description', N'{tableInfo.Description}', 'SCHEMA', N'dbo', 'TABLE', N'{tableInfo.Name}', NULL, NULL\r\n");
            int idIncre = 0;
            foreach (var column in tableInfo.Columns)
            {
                if (column.IncrementValue > 0)
                {
                    int.TryParse(column.DefaultValue, out idIncre);
                    idIncre = idIncre == 0 ? 1 : idIncre;
                }
                sb.Append($"\t[{column.Name}]{DbDataTypeFormatter.DataType(column.DataType, column.Length, column.Precision)}");
                sb.Append($"{DbDataTypeFormatter.Format(column.IncrementValue.ToString(), val => $" IDENTITY({idIncre},{column.IncrementValue})", val => int.TryParse(val, out int n) && n > 0, () => DbDataTypeFormatter.DefaultValue(column.DefaultValue, column.DataType))}");
                sb.Append($"{DbDataTypeFormatter.AllowNull(column.AllowNulls)}");
                sb.Append($",--{column.Remark}\r\n");

                sbComment.Append(
                    $"EXEC sp_addextendedproperty N'MS_Description', N'{column.Remark}', 'SCHEMA', N'dbo', 'TABLE', N'{tableInfo.Name}','COLUMN', N'{column.Name}'\r\n");
            }
            var primaryKeys = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => c.Name);
            //sb.Append($"{DbDataTypeFormatter.PrimaryKey(primaryKeys)}\r\n");
            DbDataTypeFormatter.CheckAddPrimaryKey(sb, primaryKeys, true);
            sb.Append(")\r\n");
            sb.Append(sbComment);

            if (tableInfo.Indexes.Count() > 0)
            {
                sb.Append("\r\n");
                foreach (var index in tableInfo.Indexes)
                {
                    var columns = index.ToIndexColumns();
                    if (columns.Count() == 0)
                    {
                        continue;
                    }

                    var unique = index.UniqueIndex ? " UNIQUE" : "";
                    sb.Append(
                        $"CREATE{unique} INDEX {DbDataTypeFormatter.IndexName(tableInfo.Name, columns)} ON {tableInfo.Name} {DbDataTypeFormatter.IndexColumns(columns)}\r\n");
                }
            }

            var result = new GenResult { Result = sb.ToString() };
            if (tableInfo.ExecuteSql)
            {
                result.IsExecuteSuccess = _dbAccessor.ExecuteNoneQuery(sb.ToString()) > 0;
            }

            return result;
        }

        public async Task<GenResult> GenerateAsync(DbConfig dbConfig, Table tableInfo)
        {
            return await Task.Run(() => Generate(dbConfig, tableInfo));
        }
    }
}
